/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.dados.pedagogico;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Statement;
import src.com.negocio.matricula.AgendaNivel;

/**
 *
 * @author Patricia
 */
public class AlunoDao {

    public AlunoDao() {
    }

    public void inserirAluno(String nomeCompleto, String rg, String cpf, String dtNascimento, String endereco, String numero, String bairro, String cidade, String estado, String email, String telefone, String celular, String nvEscolar) throws SQLException {
        Connection conexao;
        String in_pessoa = "INSERT INTO `bdeng2`.`pes_pessoa` (`pes_nome`, `pes_rg`, `pes_cpf`, `pes_dt_nascimento`, `pes_endereco`, `pes_numero`, `pes_bairro`, `pes_cidade`, `pes_estado`, `pes_email`, `pes_telefone`, `pes_celular`) VALUES('" + nomeCompleto + "', '"
                + rg + "', '" + cpf + "', '" + dtNascimento + "', '" + endereco + "', " + numero + ", '" + bairro + "', '" + cidade + "', '" + estado + "', '" + email + "', '" + telefone + "', '" + celular + "');";


        System.out.print(in_pessoa);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");

        Statement stmt2 = (Statement) conexao.createStatement();
        stmt2.execute(in_pessoa);

        String in_aluno = "INSERT INTO `bdeng2`.`alu_aluno` ( `alu_nivel_escolaridade`, `pes_pessoa_pes_id`) VALUES  ('" + nvEscolar + "'," + id_pessoa(nomeCompleto) + ");";

        System.out.print(in_aluno);
        Statement stmt3 = (Statement) conexao.createStatement();
        stmt3.execute(in_aluno);

        System.out.print(in_aluno);

        conexao.close();
    }

    public String id_aluno(String nome) throws SQLException {
        Connection conexao;
        String result = null;

        String select_idAluno = "SELECT alu_matricula FROM alu_aluno,pes_pessoa where pes_pessoa_pes_id = pes_id and pes_nome like '" + nome + "';";
        System.out.println(select_idAluno);

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idAluno);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }

    public String id_pessoa(String nome) throws SQLException {
        Connection conexao;
        String result = null;

        String select_idPessoa = "SELECT pes_id FROM pes_pessoa WHERE pes_nome like '" + nome + "';";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idPessoa);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;
    }

    public String id_aluno(String nome, String CPF) throws SQLException {
        Connection conexao;
        String result = null;

        String select_idAluno = "SELECT alu_matricula FROM alu_aluno,pes_pessoa where pes_pessoa_pes_id = pes_id and pes_nome like '" + nome + "' and pes_cpf like '" + CPF + "';";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idAluno);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;


    }

    public ArrayList<String> listaAlunos(String codTurma) throws SQLException {
        String select_alunos = null;

        select_alunos = "SELECT alu_matricula, pes_nome FROM pes_pessoa, tur_turma,alu_aluno,mat_matricula where pes_pessoa_pes_id = pes_id and tur_turma_tur_id = tur_id_turma and alu_aluno_alu_matricula = alu_matricula and tur_codigo = " + codTurma + ";";


        System.out.println(select_alunos);

        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_alunos);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            for (int i = 1; i < 3; i++) {
                result.add(rs.getString(i));
            }
        }


        conexao.close();
        return result;
    }

    public String verificaAluno(String nome, String CPF) throws SQLException {
        String select_count = null;

        select_count = "select count(pes_id) from pes_pessoa, alu_aluno where pes_pessoa_pes_id = pes_id and pes_nome = '" + nome + "' and pes_cpf = '" + CPF + "';";


        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String verificaAluno2(String nome) throws SQLException {
        String select_count = null;

        select_count = "select count(pes_id) from pes_pessoa, alu_aluno where pes_pessoa_pes_id = pes_id and pes_nome = '" + nome + "';";


        Connection conexao;
        String result = "0";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_count);
        ResultSet rs = stmt.executeQuery();


        while (rs.next()) {
            result = rs.getString(1);
        }


        conexao.close();
        return result;
    }

    public String retornaAluno(String matricula) throws SQLException {
        Connection conexao;
        String result = null;

        String select_idAluno = "select pes_nome from alu_aluno,pes_pessoa where pes_pessoa_pes_id = pes_id and alu_matricula = '" + matricula + "';";


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_idAluno);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            result = rs.getString(1);
        }

        conexao.close();
        return result;
    }

    public ArrayList<String> consultaAluno(String i_idioma, String c_cdTurma, String n_nvCurso) throws SQLException {

        String select_alu = null;

            String where = "";
            if (!i_idioma.equals(" ")) {
                where = where + " and idm_descricao like '" + i_idioma + "'";
            } else if (!c_cdTurma.isEmpty()) {
                where = where + " and tur_turma_tur_id = tur_id_turma and tur_codigo like '" + c_cdTurma + "'";
            } else if (!n_nvCurso.isEmpty()) {
                where = where + " and nvl_nivel_nvl_id = nvl_id and nvl_nome like '" + n_nvCurso + "'";
            }
            select_alu = "SELECT pes_nome, idm_descricao,nvl_nome, tur_codigo FROM alu_aluno, pes_pessoa, idm_idioma, nvl_nivel, tur_turma  WHERE pes_id = pes_pessoa_pes_id and idm_idioma_idm_id = idm_id and nvl_nivel_nvl_id = nvl_id " + where +";";
            System.out.println("SL02:" + select_alu);
        
        Connection conexao;
        ArrayList<String> result = new ArrayList<String>();

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_alu);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            for (int i = 1; i < 5; i++) {
                result.add(rs.getString(i));
            }
        }

        conexao.close();
        return result;
    }

    public ArrayList<String> consultaAluno2(String nome) throws SQLException {

        String select_alu = null;


        select_alu = "SELECT pes_nome, idm_descricao, nvl_nome, tur_codigo FROM alu_aluno, pes_pessoa, idm_idioma, nvl_nivel, tur_turma WHERE pes_id = pes_pessoa_pes_id and alu_nivel_escolaridade = nvl_id and nvl_id_idioma  = idm_id and pes_nome = '"+nome+"';";
        System.out.println("SL01:" + select_alu);
        Connection conexao;
        
        ArrayList<String> result = new ArrayList<String>();

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_alu);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            for (int i = 1; i < 5; i++) {
                result.add(rs.getString(i));
            }
        }

        conexao.close();
        return result;
    }
    
    public ArrayList<String> consultaAluno3(String nome) throws SQLException {

        String select_alu = null;


        select_alu = "SELECT alu_matricula, pes_nome, pes_rg,pes_cpf,pes_dt_nascimento,pes_endereco,pes_bairro,pes_numero,pes_cidade,pes_estado,pes_telefone,pes_celular,pes_email,nvl_nome FROM nvl_nivel, alu_aluno, pes_pessoa where pes_nome = '"+nome+"' and pes_pessoa_pes_id = pes_id and alu_nivel_escolaridade = nvl_id;";
        System.out.println("SL03:" + select_alu);
        Connection conexao;
        
        ArrayList<String> result = new ArrayList<String>();

        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_alu);
        ResultSet rs = stmt.executeQuery();



        while (rs.next()) {
            for (int i = 1; i < 15; i++) {
                result.add(rs.getString(i));
            }
        }

        conexao.close();
        return result;
    }
    
}